package com.ctbu.score.mapper;

import com.ctbu.score.domain.Rank;
import com.ctbu.score.domain.Score;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * 成绩Mapper接口
 **/
@Mapper
public interface ScoreMapper {

    // 根据学生学号查询成绩
    @Select("select * from score where stu_id=#{stuId}")
    List<Score> selectScoreByStuId(String stuId);

    // 查询所有成绩
    @Select("select * from score")
    List<Score> selectAllScores();

    // 根据学生学号和学期查询成绩
    @Select("select * from score where stu_id=#{stuId} And sco_term=#{scoTerm}")
    List<Score> selectScoreByStuIdAndTerm(@Param("stuId") String stuId, @Param("scoTerm") String scoTerm);

    // 插入成绩记录
    @Insert("insert into score (stu_id,sub_name,sco_num,sco_term) values (#{stuId},#{subName},#{scoNum},#{scoTerm})")
    int insertScore(Score score);

    // 根据成绩ID删除成绩记录
    @Delete("DELETE from score where sco_id=#{scoId}")
    int deleteScoreById(int scoId);

    // 根据学生学号和课程名查询成绩
    @Select("select * from score where stu_id=#{stuId} And sub_name=#{subName}")
    Score selectScoreByStuIdAndSubName(@Param("stuId") String stuId, @Param("subName") String subName);

    // 根据成绩ID查询成绩
    @Select("select * from score where sco_id=#{scoId}")
    Score selectScoreByScoId(int scoId);

    // 根据学期查询成绩排名
    @Select("select @rownum:=@rownum+1 AS rownum, stu_id, stu_allsco, sco_term from (SELECT @rownum:=0) r" +
            ", (select sco_term, stu_id, sum(sco_num) as 'stu_allsco' from score, (SELECT @rownum:=0) r\n" +
            "where  sco_term=#{scoTerm} GROUP BY stu_id ORDER BY sum(sco_num) DESC) w")
    List<Rank> selectRankByTerm(String scoTerm);

    // 根据学生学号和学期查询成绩，并返回Map形式的结果
    @Select("select sub_name, sco_num from score where stu_id=#{stuId} and sco_term=#{scoTerm}")
    List<Map<String, Integer>> selectScoreMap(@Param("stuId") String stuId, @Param("scoTerm") String scoTerm);

    // 根据学生学号列表和学期查询成绩排名
    @Select({"<script>",
            "select @rownum:=@rownum+1 AS rownum, stu_id, stu_allsco, sco_term from (SELECT @rownum:=0) r" +
                    ", (select sco_term, stu_id, sum(sco_num) as 'stu_allsco' from score, (SELECT @rownum:=0) r\n" +
                    "where  sco_term=#{scoTerm} and stu_id in",
            "<foreach collection='stuId' item='stuId' open='(' separator=',' close=')'>",
            "#{stuId}",
            "</foreach>",
            "GROUP BY stu_id ORDER BY sum(sco_num) DESC) w",
            "</script>"})
    List<Rank> selectRankByTermAndStuId(@Param("stuId") List<String> stuId, @Param("scoTerm") String scoTerm);
}

